﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Configuration;
using System.Data;
using System.Windows.Forms;
using GS1.Entidad;

namespace GS1.Dato
{
    public class DatosLite : IDisposable
    {
        #region --- Variables Privadas ---
        private SQLiteDataAdapter adapter;
        private SQLiteCommand comando;
        private SQLiteConnection conexion;

        private static string NOMBRE_REPOSITORIO = "Repositorio";
        #endregion

        #region --- Constructor y Destructor ---
        public DatosLite()
        {
            string sRutaBD = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location) +ConfigurationManager.AppSettings[NOMBRE_REPOSITORIO];

            conexion = new SQLiteConnection(string.Format("Data Source={0};New={1};Compress=False;Version={2}", sRutaBD,
                                                                                                                 false,
                                                                                                                 3));
        }

        public void Dispose()
        {
            if (this.conexion != null)
            {
                if (this.conexion.State == ConnectionState.Open)
                {
                    this.conexion.Close();
                }
                this.conexion.Dispose();
            }
            this.conexion = null;
        }
        #endregion

        #region --- Procedimientos Genericos ---

        public DataTable GEN_Consulta(string pCadSQL, string pNombreTabla)
        {
            DataTable dataTable = new DataTable(pNombreTabla);
            if (this.conexion.State == ConnectionState.Open)
            {
                this.conexion.Close();
            }
            try
            {
                this.adapter = new SQLiteDataAdapter(pCadSQL, this.conexion);
                this.adapter.Fill(dataTable);
            }
            catch (SQLiteException exception1)
            {
                throw (exception1);
            }
            catch (Exception exception3)
            {
                throw (exception3);
            }
            finally
            {
                this.adapter.Dispose();
            }
            return dataTable;
        }

        public DataSet GEN_Consulta(string pCadSQL)
        {
            DataSet oData = new DataSet();
            if (this.conexion.State == ConnectionState.Open) { this.conexion.Close(); }

            try
            {
                this.adapter = new SQLiteDataAdapter(pCadSQL, this.conexion);
                this.adapter.Fill(oData);
            }
            catch (SQLiteException exception1)
            {
                throw (exception1);
            }
            catch (Exception exception3)
            {
                throw (exception3);
            }
            finally
            {
                this.adapter.Dispose();
            }
            return oData;
        }

        public bool GEN_EjecutaComando(SQLiteConnection pConexion, string pComandoTxt)
        {
            this.comando = new SQLiteCommand();
            try
            {
                this.comando.Connection = pConexion;
                this.comando.CommandText = pComandoTxt;
                this.comando.CommandType = CommandType.Text;
                this.conexion = this.ConectarBD();
                this.comando.ExecuteNonQuery();
            }
            catch (SQLiteException exception1)
            {
                throw (exception1);
            }
            catch (Exception exception3)
            {
                throw (exception3);
            }
            finally
            {
                this.conexion.Close();
                this.comando.Dispose();
                this.comando = null;
            }
            return false;
        }

        #endregion

        #region --- ConexionBD ---
        private SQLiteConnection ConectarBD()
        {
            if (this.conexion.State == ConnectionState.Closed)
            {
                this.conexion.Open();
            }
            return this.conexion;
        }

        #endregion

        #region --- Procedimientos BD ---
        public bool guardarTagEpc(TagEPC tagEPC)
        {
            bool resultado = false;
            StringBuilder cadenaSQL = null;
            SQLiteTransaction transaction = null;

            try
            {
                cadenaSQL = new StringBuilder();
                cadenaSQL.Append("INSERT INTO ETIQUETA (EPC, ANTENA, FECHA) ");
                cadenaSQL.Append("VALUES (?, ?, ?)");

                this.comando = new SQLiteCommand();
                this.conexion = this.ConectarBD();
                transaction = this.conexion.BeginTransaction();

                this.comando.Connection = this.conexion;
                this.comando.CommandText = cadenaSQL.ToString();
                this.comando.CommandType = CommandType.Text;

                this.comando.Prepare();

                this.comando.Parameters.Add("EPC", DbType.String);
                this.comando.Parameters.Add("ANTENA", DbType.String);
                this.comando.Parameters.Add("FECHA", DbType.DateTime);

                this.comando.Parameters["EPC"].Value = tagEPC.epc;
                this.comando.Parameters["ANTENA"].Value = tagEPC.antena;
                this.comando.Parameters["FECHA"].Value = tagEPC.fecha;

                this.comando.ExecuteNonQuery();

                transaction.Commit();

                resultado = true;
            }
            finally
            {
                if ((this.comando != null) && (this.comando.Connection != null))
                {
                    this.comando.Connection.Close();
                }
                this.conexion.Close();
            }
            return resultado;
        }

        public bool validarTagEpc(TagEPC tagEPC)
        {
            DataTable tablaResultado = null;
            StringBuilder cadenaSQL = null;

            cadenaSQL = new StringBuilder();
            cadenaSQL.Append("SELECT EPC, ANTENA, FECHA ");
            cadenaSQL.Append("FROM ETIQUETA ");
            cadenaSQL.Append("WHERE EPC = '" + tagEPC.epc + "' ");
            cadenaSQL.Append("AND ANTENA = '" + tagEPC.antena + "' ");
            cadenaSQL.Append("AND FECHA = '" + tagEPC.fecha + "' ");

            tablaResultado = this.GEN_Consulta(cadenaSQL.ToString(), "TablaMovimiento");

            return tablaResultado.Rows.Count > 0;
        }

        public bool eliminarTagEpc(DateTime dia)
        {
            bool resultado = false;
            StringBuilder cadenaSQL = null;
            SQLiteTransaction transaction = null;

            try
            {
                cadenaSQL = new StringBuilder();
                cadenaSQL.Append("DELETE FROM ETIQUETA ");
                cadenaSQL.Append("WHERE FECHA < '" + dia.ToString("yyyy-MM-dd HH:mm:ss") + "' ");

                this.comando = new SQLiteCommand();
                this.conexion = this.ConectarBD();
                transaction = this.conexion.BeginTransaction();

                this.comando.Connection = this.conexion;
                this.comando.CommandText = cadenaSQL.ToString();
                this.comando.CommandType = CommandType.Text;

                this.comando.ExecuteNonQuery();

                transaction.Commit();

                resultado = true;
            }
            finally
            {
                if ((this.comando != null) && (this.comando.Connection != null))
                {
                    this.comando.Connection.Close();
                }
                this.conexion.Close();
            }
            return resultado;
        }

        public bool modificarTagEpc(TagEPC tagEpc)
        {
            bool resultado = false;
            StringBuilder cadenaSQL = null;
            SQLiteTransaction transaction = null;

            try
            {
                cadenaSQL = new StringBuilder();
                cadenaSQL.Append("UPDATE ETIQUETA SET estado = '0' ");
                cadenaSQL.Append("WHERE EPC = '" + tagEpc.epc + "' ");
                cadenaSQL.Append("AND ANTENA = '" + tagEpc.antena + "' ");
                cadenaSQL.Append("AND estado = '1' ");

                this.comando = new SQLiteCommand();
                this.conexion = this.ConectarBD();
                transaction = this.conexion.BeginTransaction();

                this.comando.Connection = this.conexion;
                this.comando.CommandText = cadenaSQL.ToString();
                this.comando.CommandType = CommandType.Text;

                this.comando.ExecuteNonQuery();

                transaction.Commit();

                resultado = true;
            }
            finally
            {
                if ((this.comando != null) && (this.comando.Connection != null))
                {
                    this.comando.Connection.Close();
                }
                this.conexion.Close();
            }
            return resultado;
        }

        public bool modificarTagEpcBloque(DateTime Hora)
        {
            bool resultado = false;
            StringBuilder cadenaSQL = null;
            SQLiteTransaction transaction = null;

            try
            {
                cadenaSQL = new StringBuilder();
                cadenaSQL.Append("UPDATE ETIQUETA SET estado = '0' ");
                cadenaSQL.Append("WHERE FECHA < '" + Hora.ToString("yyyy-MM-dd HH:mm:ss") + "' ");
                cadenaSQL.Append("AND estado = '1' ");

                this.comando = new SQLiteCommand();
                this.conexion = this.ConectarBD();
                transaction = this.conexion.BeginTransaction();

                this.comando.Connection = this.conexion;
                this.comando.CommandText = cadenaSQL.ToString();
                this.comando.CommandType = CommandType.Text;

                this.comando.ExecuteNonQuery();

                transaction.Commit();

                resultado = true;
            }
            finally
            {
                if ((this.comando != null) && (this.comando.Connection != null))
                {
                    this.comando.Connection.Close();
                }
                this.conexion.Close();
            }
            return resultado;
        }

        public ListaTagEPC ListaTagEPCIncidencia()
        {
            DataTable tablaResultado = null;
            StringBuilder cadenaSQL = null;
            ListaTagEPC listaTagEPC = new ListaTagEPC();

            cadenaSQL = new StringBuilder();
            cadenaSQL.Append("SELECT EPC, ANTENA, FECHA  ");
            cadenaSQL.Append("FROM ETIQUETA ");
            cadenaSQL.Append("WHERE estado = '1' ");
            cadenaSQL.Append("AND EPC NOT IN ( SELECT EPC FROM ETIQUETA ");
            cadenaSQL.Append("WHERE ANTENA = '17'  ");
            cadenaSQL.Append("AND estado = '1' ) ");

            tablaResultado = this.GEN_Consulta(cadenaSQL.ToString(), "TablaTemporal");

            if (tablaResultado.Rows.Count > 0)
            {
                foreach (DataRow fila in tablaResultado.Rows)
                {
                    TagEPC tagEpc = new TagEPC();
                    tagEpc.epc = fila["EPC"].ToString();
                    tagEpc.antena = fila["ANTENA"].ToString();
                    tagEpc.fecha = Convert.ToDateTime(fila["FECHA"]);

                    listaTagEPC.Add(tagEpc);
                }
            }

            return listaTagEPC;
        }

        public ListaTagEPC ListaTagEPC(TagEPC tagEpc)
        {
            DataTable tablaResultado = null;
            StringBuilder cadenaSQL = null;
            ListaTagEPC listaTagEPC = new ListaTagEPC();

            cadenaSQL = new StringBuilder();
            cadenaSQL.Append("SELECT EPC, ANTENA, FECHA  ");
            cadenaSQL.Append("FROM ETIQUETA ");
            cadenaSQL.Append("WHERE EPC = '" + tagEpc.epc + "' ");
            cadenaSQL.Append("AND ANTENA = '" + tagEpc.antena + "' ");
            cadenaSQL.Append("AND estado = '1' ");

            tablaResultado = this.GEN_Consulta(cadenaSQL.ToString(), "TablaTemporal");

            if (tablaResultado.Rows.Count > 0)
            {
                foreach (DataRow fila in tablaResultado.Rows)
                {
                    TagEPC tagEpcConsulta = new TagEPC();
                    tagEpcConsulta.epc = fila["EPC"].ToString();
                    tagEpcConsulta.antena = fila["ANTENA"].ToString();
                    tagEpcConsulta.fecha = Convert.ToDateTime(fila["FECHA"]);

                    listaTagEPC.Add(tagEpcConsulta);
                }
            }

            return listaTagEPC;
        }


        #endregion

    }
}
